package zy.dao.sys.user.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sys.user.UserDAO;
import zy.entity.sys.user.T_Sys_User;
import zy.util.CommonUtil;
import zy.util.MD5;
@Repository
public class UserDAOImpl extends BaseDaoImpl implements UserDAO{

	@Override
	public Integer count(Map<String, Object> param) {
		Object name = param.get("name");
		Object ty_id = param.get("ty_id");
		Object sp_code = param.get("sp_code");
		Object ro_code = param.get("ro_code");
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_sys_user t");
		sql.append(" join t_base_shop s");
		sql.append(" on s.sp_code=t.us_shop_code");
		sql.append(" and s.companyid=t.companyid");
		if(shop_type.equals(CommonUtil.ONE) || shop_type.equals(CommonUtil.TWO)){
			if(null != sp_code && !"".equals(sp_code)){
				sql.append(" and s.sp_code=:sp_code");
			}
			sql.append(" and s.sp_upcode=:shop_code");
		}else{
			sql.append(" and s.sp_code=:shop_code");
		}
		sql.append(" join common_type ty");
		sql.append(" on ty.ty_id=s.sp_shop_type");
		sql.append(" and instr(ty.ty_type,:shop_type)>0");
		if(null != ty_id && !"".equals(ty_id)){
			sql.append(" and ty.ty_id=:ty_id");
		}
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and (instr(t.us_name,:name)>0 or instr(t.us_account,:name)>0)");
        }
        if(null != ro_code && !"".equals(ro_code)){
			sql.append(" and t.us_ro_code=:ro_code");
		}
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Sys_User> list(Map<String, Object> param) {
		Object name = param.get("name");
		Object ty_id = param.get("ty_id");
		Object sp_code = param.get("sp_code");
		Object ro_code = param.get("ro_code");
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select us_id,us_code,us_account,us_name,ty_name,us_default,");
		sql.append(" sp_name as shop_name,ro_name,us_state,us_pay,us_last,us_end");
		sql.append(" from t_sys_user t");
		sql.append(" join t_sys_role r");
		sql.append(" on r.ro_code=t.us_ro_code");
		sql.append(" and r.companyid=t.companyid");
		sql.append(" join t_base_shop s");
		sql.append(" on s.sp_code=t.us_shop_code");
		sql.append(" and s.companyid=t.companyid");
		if(shop_type.equals(CommonUtil.ONE) || shop_type.equals(CommonUtil.TWO)){
			if(null != sp_code && !"".equals(sp_code)){
				sql.append(" and s.sp_code=:sp_code");
			}
			sql.append(" and s.sp_upcode=:shop_code");
		}else{
			sql.append(" and s.sp_code=:shop_code");
		}
		sql.append(" join common_type ty");
		sql.append(" on ty.ty_id=s.sp_shop_type");
		sql.append(" and instr(ty.ty_type,:shop_type)>0");
		if(null != ty_id && !"".equals(ty_id)){
			sql.append(" and ty.ty_id=:ty_id");
		}
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and (instr(t.us_name,:name)>0 or instr(t.us_account,:name)>0)");
        }
        if(null != ro_code && !"".equals(ro_code)){
			sql.append(" and t.us_ro_code=:ro_code");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by us_id desc");
		sql.append(" limit :start,:end");
		List<T_Sys_User> list = namedParameterJdbcTemplate.query(sql.toString()
				, param, new BeanPropertyRowMapper<>(T_Sys_User.class));
		return list;
	}

	@Override
	public Integer queryByName(T_Sys_User model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select us_id from t_sys_user");
		sql.append(" where 1=1");
		if(null != model.getUs_account() && !"".equals(model.getUs_account())){
			sql.append(" and us_account=:us_account");
		}
		if(null != model.getUs_id() && model.getUs_id() > 0){
			sql.append(" and us_id <> :us_id");
		}
		sql.append(" and companyid=:companyid");
		sql.append(" limit 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new BeanPropertySqlParameterSource(model) ,Integer.class);
			return id;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public T_Sys_User queryByID(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select us_id,us_code,us_account,us_name,ro_shop_type as shoptype,us_default,");
		sql.append(" sp_name as shop_name,us_shop_code,us_limit,ro_name,us_ro_code,us_state");
		sql.append(" from t_sys_user t");
		sql.append(" join t_sys_role r");
		sql.append(" on r.ro_code=t.us_ro_code");
		sql.append(" and r.companyid=t.companyid");
		sql.append(" join t_base_shop s");
		sql.append(" on s.sp_code=t.us_shop_code");
		sql.append(" and s.companyid=t.companyid");
		sql.append(" where us_id=:us_id");
		try{
			T_Sys_User data = namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("us_id", id),
					new BeanPropertyRowMapper<>(T_Sys_User.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void update(T_Sys_User model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE T_Sys_User");
		sql.append(" SET us_account=:us_account");
		sql.append(" ,us_name=:us_name");
		sql.append(" ,us_shop_code=:us_shop_code");
		sql.append(" ,us_ro_code=:us_ro_code");
		sql.append(" ,us_limit=:us_limit");
		sql.append(" WHERE us_id=:us_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public void updateState(T_Sys_User model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE T_Sys_User");
		sql.append(" SET us_state=:us_state");
		sql.append(" WHERE us_id=:us_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public void save(T_Sys_User model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select f_three_code(max(us_code+0)) from T_Sys_User ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		model.setUs_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO T_Sys_User");
		sql.append(" (us_code,us_account,us_name,us_pass,us_state,us_date,us_end,");
		sql.append(" us_pay,us_default,us_ro_code,us_shop_code,us_limit,companyid)");
		sql.append(" VALUES");
		sql.append(" (:us_code,:us_account,:us_name,:us_pass,:us_state,:us_date,:us_end,");
		sql.append(" :us_pay,:us_default,:us_ro_code,:us_shop_code,:us_limit,:companyid)");
		
		KeyHolder holder = new GeneratedKeyHolder(); 
		
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model),holder);
		int id = holder.getKey().intValue();
		model.setUs_id(id);
	}

	@Override
	public void del(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM T_Sys_User");
		sql.append(" WHERE us_id=:us_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("us_id", id));
	}

	@Override
	public void initPwd(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE T_Sys_User");
		sql.append(" SET us_pass=:us_pass");
		sql.append(" WHERE us_id=:us_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("us_id", id).addValue("us_pass", MD5.encryptMd5("123456")));
	}

	@Override
	public String getPwd(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT us_pass FROM t_sys_user");
		sql.append(" WHERE 1=1");
		sql.append(" AND us_id = :us_id");
		sql.append(" limit 1");
		String pwd = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("us_id", id), String.class);
		return pwd;
	}

	@Override
	public void changePassword(Integer id, String us_pwd) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE T_Sys_User");
		sql.append(" SET us_pass=:us_pass");
		sql.append(" WHERE us_id=:us_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("us_id", id).addValue("us_pass", us_pwd));
	}
}
